<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class adminModel extends APPS_Model {

    function __construct() {
        parent::__construct();
    }

	function getRef($reference=null, $value=null, $type = "col", $mode="id" ) {
		$table      = "ref_" . $reference;
		$field_id   = $reference. "_id";
		$field_name = $reference;
		$field_var  = $reference. "_var";

		if ($mode=="var") $field_id = $field_var;
		$field      = $type=="col" ? $field_name : $field_id.", ".$field_var.",". $field_name;
		$sql_type   = $type=="col" ? "col" : "fetch";

		$sql = "select $field from $table where $field_id in ($value)";
		return $this->result($sql);
	}


	function getLogin($post) {
		//$sql = "SELECT * FROM app_user WHERE user_name ='$post[username]' and pass ='".md5($post['passwd'])."'";
		$sql = "SELECT * FROM app_user WHERE user_name ='$post[username]' and pass ='".$post['passwd']."'";
		return $this->result($sql,"row");
	}

	function getContent($order=null, $sort=null, $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " contentID ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.title, a.state, a.created, 
							a.contentID" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where 1=1 ";

		$sql = "SELECT $col 
				  FROM content a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}


	public  function getContentForm($id=0) {
				$sql = "SELECT a.contentID, a.title, a.fulltext, a.state, a.slideshow
				  FROM content a
				  WHERE a.contentID  = $id";
		
		return $this->result($sql, "row");
	}

	
	function getFasilitas($order=null, $sort=null, $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " contentID ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.title, a.state, a.created, 
							a.contentID" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where categoryID = 7 ";

		$sql = "SELECT $col 
				  FROM content a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}

	function getPejabat($order='orderby', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " orderby ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.nama, a.jabatan, a.state, a.created, 
							a.ID" : "count(*) count";
		$ord = $limit ? "order by orderby" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where 1=1 ";

		$sql = "SELECT $col 
				  FROM pejabat  a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	


	public  function getPejabatForm($id=0) {
		$sql = "SELECT id, nama, jabatan, orderby, state 
				  FROM pejabat
				  WHERE id  = $id";
		return $this->result($sql, "row");
	}

	function getPengajar($order='nama', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " nama ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.nama, a.nippns, a.state, a.created, 
							a.id" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where 1=1 ";

		$sql = "SELECT $col 
				  FROM dosen  a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	


	public  function getPengajarForm($id=0) {
		$sql = "SELECT id, nama, nippns, state 
				  FROM dosen
				  WHERE id  = '$id'";
		return $this->result($sql, "row");
	}

	function getUser($order=null, $sort=null, $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " name ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.user_id,a.user_name, a.name, a.state, a.created " : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where 1=1 ";

		$sql = "SELECT $col 
				  FROM app_user a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getUserForm($id=0) {
		$sql = "SELECT * 
				  FROM app_user
				  WHERE user_id  = '$id'";
		return $this->result($sql, "row");
	}	

	public  function getAcl() {
		$sql = "SELECT * 
				  FROM ref_acl order by acl";
		return $this->result($sql);
	}	

	function getSection($order=null, $sort=null, $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " name ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.* " : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where 1=1 ";

		$sql = "SELECT $col 
				  FROM section a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getSectionForm($id=0) {
		$sql = "SELECT * 
				  FROM section
				  WHERE sectionid  = '$id'";
		return $this->result($sql, "row");
	}		

		public  function getSectionRef() {
		$sql = "SELECT * 
				  FROM section
				  order by title";
		return $this->result($sql);
	}		

		
	function getCategory($order=null, $sort=null, $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " name ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.*, b.title section " : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where 1=1 ";

		$sql = "SELECT $col 
				  FROM category a			
				left join section b on a.sectionid = b.sectionid
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getCategoryForm($id=0) {
		$sql = "SELECT * 
				  FROM category
				  WHERE categoryid  = '$id'";
		return $this->result($sql, "row");
	}		
	

	function getBerita($order=null, $sort=null, $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " contentID ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.title, a.state, a.created, 
							a.contentID" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where a.categoryID = 13";
		
		$sql = "SELECT $col 
				  FROM content a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	function getImage2x($contentID=0) {
		$order = strlen($order) >0  ? $order : " contentID ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.title, a.state, a.created, 
							a.contentID" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where a.categoryID = 13";

		$sql = "SELECT $col 
				  FROM content a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	function setState($id, $state) {
		$sql = "UPDATE content set state = $state 
				WHERE contentID = $id";
		$this->db->query($sql);			
	}

	function setUserState($id, $state) {
		$sql = "UPDATE app_user  set state = $state 
				WHERE user_id = $id";
		$this->db->query($sql);			
	}	
	public  function getBeritaEdit($id=0) {
				$sql = "SELECT a.contentID, a.title, a.fulltext, a.state, a.slideshow
				  FROM content a
				  WHERE a.contentID  = $id";
		
		return $this->result($sql, "row");
	}
	
	public  function getImage($id=0) {
				$sql = "SELECT a.imgID, a.img
				  FROM content_img a
				  WHERE a.contentID  = $id";
		return $this->result($sql);
	}
	
	public  function getImageHeadline($id=0) {
				$sql = "SELECT a.img
				  FROM content_img a
				  WHERE a.contentID  = $id and a.img_type like '%headline%'";
		return $this->result($sql, "col");
	}

	public  function getImage1($id=0) {
				$sql = "SELECT a.img
				  FROM content_img a
				  WHERE a.contentID  = $id and a.img_type like '%img1%'";
		return $this->result($sql, "col");
	}

	public  function getImage2($id=0) {
				$sql = "SELECT a.img
				  FROM content_img a
				  WHERE a.contentID  = $id and a.img_type like '%img2%'";
		return $this->result($sql, "col");
	}

	function getKuliah($order='nama', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " nama ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.nama, a.sks, a.semester, a.jenis, 
							a.id" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where 1=1 ";

		$sql = "SELECT $col 
				  FROM makul  a				  
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	

	/* mi2208 */
	public  function getKuliahForm($id=0) {
		$sql = "SELECT a.nama, a.sks, a.semester, a.jenis, a.id
				  FROM makul a
				  WHERE a.id  = '$id'";
		return $this->result($sql, "row");
	}
	/* end mi2208 */

	/* ilham 06:59 not uploaded */
	function getSilabus($order='semester', $sort='desc', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " semester ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}
		
		$col = $limit ? "a.id, a.mataKuliah, a.jenis, a.semester, a.sks, a.kode, 
						 b.jurusan" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where a.jurusanID = 3 ";

		$sql = "SELECT $col 
				  FROM silabus a
				  left join ref_jurusan b on a.jurusanID = b.jurusanID
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getSilabusForm($id=0) {
		$sql = "SELECT a.id, a.mataKuliah, a.jenis, a.semester, a.sks, a.kode, b.jurusan
				  FROM silabus a
				  left join ref_jurusan b on a.jurusanID = b.jurusanID
				  WHERE a.id  = '$id'";
		return $this->result($sql, "row");
	}
	/* 06:59 */
	
	/* Daftar Mahasiswa 220810 22:16 */
	function getMahasiswa($order='nama', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " nama ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.nama, a.id, a.angkatan, b.nama jurusan,
					     a.kelas" : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where a.angkatan in ('2007', '2008', '2009') ";

		$sql = "SELECT $col 
				  FROM mahasiswa a
				  left join prodi b on a.idprodi = b.id 
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getMahasiswaForm($id=0) {
		$sql = "SELECT a.nama, a.id, a.angkatan, b.nama jurusan,
					     a.kelas
				  FROM mahasiswa a
  				  left join prodi b on a.idprodi = b.id 
				  WHERE a.id  = '$id'";
		return $this->result($sql, "row");
	}
	
	public function getJurusan() {
		$sql = "SELECT a.id, a.nama FROM prodi a";
		
		return $this->result($sql);
	}
	/* end here Daftar Mahasiswa */

	/* Kelulusan 230810 01:55 */
	function getLulus($order='nama', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " nama ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}

		$col = $limit ? "a.nama, a.id, b.jurusan
					     " : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "where a.lulus = '2005'";

		$sql = "SELECT $col 
				  FROM alumni a
				  left join ref_jurusan b on a.jurusanID = b.jurusanID
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getLulusForm($id=0) {
		$sql = "SELECT a.nama, a.id, b.jurusan
				  FROM alumni a
				  left join ref_jurusan b on a.jurusanID = b.jurusanID
				  WHERE a.id  = '$id'";
		return $this->result($sql, "row");
	}
	/* end here kelulusan */

	/* Alummni 230810 02:34 */
	function getAlumni($order='nama', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " nama ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}


		$col = $limit ? "a.nama, a.id, a.angkatan, b.jurusan jurusan, a.lulus
					     " : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "WHERE 1 = 1 ";

		$sql = "SELECT $col 
				  FROM alumni a
				  LEFT JOIN ref_jurusan  b on a.jurusanid  = b.jurusanid
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getAlumniForm($id=0) {
		$sql = "SELECT a.nama, a.id, a.angkatan, b.jurusan jurusan, a.lulus
				  FROM alumni a
  				  LEFT JOIN ref_jurusan  b on a.jurusanid  = b.jurusanid 
				  WHERE a.id  = '$id'";
		return $this->result($sql, "row");
	}
	
	/* end here alumni */

	/* Kalender Akademik */
	function getKalender($order='id', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " id ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}


		$col = $limit ? "a.* " : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "WHERE 1 = 1 ";

		$sql = "SELECT $col 
				  FROM kalender a
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getKalenderForm($id=0) {
		$sql = "SELECT a.*
				  FROM kalender a
				  WHERE a.id  = '$id'";
		return $this->result($sql, "row");
	}
	
	/* end here kalender */

	/* Kalender Akademik */
	function getPustaka($order='id', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " id ";
		
		$ascdes = "ASC";
		if ($sort == "none") {
			$ascdes = "ASC";
		} else if ($sort == "asc") {
			$ascdes = "DESC";
		} else if ($sort == "desc") {
			$ascdes = "ASC";
		}


		$col = $limit ? "a.* " : "count(*) count";
		$ord = $limit ? "order by a.$order $ascdes" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "WHERE 1 = 1 ";

		$sql = "SELECT $col 
				  FROM perpustakaan a
				  $whr $ord $lim ";
				   
		return $this->result($sql);
	}
	
	public  function getPustakaForm($id=0) {
		$sql = "SELECT a.*
				  FROM perpustakaan a
				  WHERE a.id  = '$id'";
		return $this->result($sql, "row");
	}
	
	function getPendaftaranExport(){
		$sql = "SELECT * FROM student_register
				WHERE IFNULL(VALID_TO, now()+1) > now()					
					AND LOGIN_DATE IS NOT NULL";
					
       	return $this->result($sql);
	}
	
	function getPendaftaran($order='ID', $sort='ASC', $limit=0, $offset=0) {
		$order = strlen($order) >0  ? $order : " ID ";		
		$ascdes = "ASC";
		if ($sort == "none") 
		{			
			$ascdes = "ASC";		
		} 
		else if ($sort == "asc") 
		{			
			$ascdes = "DESC";		
		} 
		else if ($sort == "desc") 
		{			
			$ascdes = "ASC";
		}
		
		/*$col = $limit ? "a.* " : "count(*) count";*/
		$col = $limit ? "
			a.ID, 
			a.no_pendaftaran, 
			a.nama,
			c.jurusan,						
			d.jurusan AS jurusan_smta,						a.tempat_lahir,						
			a.tgl_lahir,						
			a.jenis_kelamin,						
			a.tinggi_badan,						
			a.tahun_lulus,						
			b.jenis,						
			a.register_date,						
			e.lokasi AS lokasiTKD,						
			f.lokasi AS lokasiTKA" 						: "count(*) count";
			
		$ord = $limit ? "order by a.ID $ascdes" : "";		
		$lim = $limit ? "limit $limit offset $offset" : "";		
		$whr = "WHERE 1 = 1 					
			AND IFNULL(a.VALID_TO, now()+1) > now()					
			AND a.LOGIN_DATE IS NOT NULL";
			
		$sql = "SELECT $col 				
			FROM					
			student_register a				
				LEFT JOIN ref_jenis_smta b ON a.jenis_smtaID = b.jenis_smtaID				
				LEFT JOIN ref_jurusan_smta c ON a.jurusan_smtaID = c.jurusan_smtaID				LEFT JOIN ref_jurusan d ON a.jurusanID = d.jurusanID				
				LEFT JOIN ref_lokasi_tkd e ON a.lokasiTKD = e.ref_lokasi_tkd_id				
				LEFT JOIN ref_lokasi_tka f ON a.lokasiTKA = f.ref_lokasi_tka_id								  
			$whr $ord $lim ";
		
		return $this->result($sql);	
	}
	
	function getReffLokasi(){
        $sql = "select * from ref_lokasi order by code";
        return $this->result($sql);     
   	}
   	
	public function getPendaftaranDetail($no_pendaftaran=0){
        $sql = "SELECT a.no_pendaftaran, a.nama, c.jurusan as jurusan_smtaID, d.jurusan as jurusanID,
						g.lokasi as lokasiID, a.tempat_lahir, a.tgl_lahir, a.jenis_kelamin,
						a.tinggi_badan, e.agama as agama, f.propinsi as propinsiID, a.tahun_lulus, b.jenis as jenis_smtaID,
						a.mat_4, a.fisika_4, a.inggris_4,
						a.mat_5, a.fisika_5, a.inggris_5,
						a.mat_6, a.fisika_6, a.inggris_6,
						a.register_date, a.email, a.alamat, a.kodepos, a.telepon, a.foto,
						h.BILLING_NO
				FROM student_register a
				LEFT JOIN ref_jenis_smta b ON a.jenis_smtaID = b.jenis_smtaID
				LEFT JOIN ref_jurusan_smta c ON a.jurusan_smtaID = c.jurusan_smtaID
				LEFT JOIN ref_jurusan d ON a.jurusanID = d.jurusanID
				LEFT JOIN ref_agama e ON a.agama = e.agamaID
				LEFT JOIN ref_propinsi f ON a.propinsiID = f.propinsiID
				LEFT JOIN ref_lokasi g ON a.lokasiID = g.lokasiID
				LEFT JOIN T_BILLING h ON a.t_billing_id = h.T_BILLING_ID
				where a.no_pendaftaran = '$no_pendaftaran'";
       	return $this->result($sql,"row");
	}
	
		public function getPin($order='pinID', $sort='ASC', $limit=0, $offset=0){
			
			$order = strlen($order) >0  ? $order : " pinID ";
		
			$ascdes = "ASC";
			if ($sort == "none") {
				$ascdes = "ASC";
			} else if ($sort == "asc") {
				$ascdes = "DESC";
			} else if ($sort == "desc") {
				$ascdes = "ASC";
			}
			
				$col = $limit ? "a.pin, a.pinID, a.state" 
											: "count(*) count";
				
				$ord = $limit ? "order by a.$order $ascdes" : "";
				$lim = $limit ? "limit $limit offset $offset" : "";
				$whr = "WHERE 1 = 1 ";
				
				$sql = "SELECT $col 
				  FROM pin a  
				  $whr $ord $lim ";
				
				return $this->result($sql);
	}
	
		public function getPinAvailable($order='pinID', $sort='ASC', $limit=0, $offset=0){
			
			$order = strlen($order) >0  ? $order : " pinID ";
		
			$ascdes = "ASC";
			if ($sort == "none") {
				$ascdes = "ASC";
			} else if ($sort == "asc") {
				$ascdes = "DESC";
			} else if ($sort == "desc") {
				$ascdes = "ASC";
			}
			
				$col = $limit ? "a.pin, a.pinID, a.state" 
											: "count(*) count";
				
				$ord = $limit ? "order by a.$order $ascdes" : "";
				$lim = $limit ? "limit $limit offset $offset" : "";
				$whr = "WHERE state = 0 ";
				
				$sql = "SELECT $col 
				  FROM pin a  
				  $whr $ord $lim ";
				
				return $this->result($sql);
	}
	
			
}
	
/* End of file mreg.php */